package com.zzmcc.jms.controller;

import com.jfinal.aop.Before;
import com.jfinal.ext.kit.PoiExt;
import com.jfinal.ext.render.excel.JxlsRender;
import com.jfinal.kit.PathKit;
import com.jfinal.log.Logger;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.upload.UploadFile;
import com.zzmcc.base.BaseController;
import com.zzmcc.common.AuthorizeInterceptor;
import com.zzmcc.jms.model.Project;
import util.ChristStringUtil;
import util.ModelUtils;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.zzmcc.jms.common.ModelConfigWms.TABLE_Project;

public class DemandController0830 extends BaseController {
    private static final Logger log =  Logger.getLogger(DemandController0830.class);
 	public void index() {

        StringBuffer whee = new StringBuffer();
        List<Object> param = new ArrayList<Object>();
        String name = getPara("demand.name");
		String memo = getPara("demand.memo");
		String demandMemo = getPara("demand.demandMemo");
		String code = getPara("demand.code");
		String isSelect = getPara("demand.isSelect");
		String isSelectConfirm = getPara("demand.isSelectConfirm");
		String isEntrust = getPara("demand.isEntrust");
		String isCm = getPara("demand.isCm");
		String isCt = getPara("demand.isCt");
		String isCu = getPara("demand.isCu");
		String isCmOrderConfirm = getPara("demand.isCmOrderConfirm");
		String isCtOrderConfirm = getPara("demand.isCtOrderConfirm");
		String isCuOrderConfirm = getPara("demand.isCuOrderConfirm");

		String cmCreateDate = getPara("demand.cmCreateDate");
		String ctCreateDate = getPara("demand.ctCreateDate");
		String cuCreateDate = getPara("cmCreateDate.cuCreateDate");

		String demandOwner = getPara("demand.demandOwner");
        String beginDate = getPara("beginDate");
		String endDate = getPara("endDate");
		String homeCounty = getPara("demand.homeCounty");

        if(ChristStringUtil.isNotEmpty(name)){
            whee.append(" and name like ?");
            param.add("%" + name + "%");
        }

		if(ChristStringUtil.isNotEmpty(demandMemo)){
			whee.append(" and demandMemo like ?");
			param.add("%" + demandMemo + "%");
		}

		if(ChristStringUtil.isNotEmpty(memo)){
			whee.append(" and memo like ?");
			param.add("%" + memo + "%");
		}

		if(ChristStringUtil.isNotEmpty(code)){
			whee.append(" and code like ?");
			param.add("%" + code + "%");
		}
		if(ChristStringUtil.isNotEmpty(demandOwner)){
			whee.append(" and demandOwner = ?");
			param.add(demandOwner);
		}

		if(ChristStringUtil.isNotEmpty(homeCounty)){
			whee.append(" and homeCounty = ?");
			param.add(homeCounty);
		}
		if(ChristStringUtil.isNotEmpty(isSelect)){
			whee.append(" and isSelect = ?");
			param.add(isSelect);
		}
		if(ChristStringUtil.isNotEmpty(isEntrust)){
			whee.append(" and isEntrust = ?");
			param.add(isEntrust);
		}
		if(ChristStringUtil.isNotEmpty(isCm)){
			whee.append(" and isCm = ?");
			param.add(isCm);
		}
		if(ChristStringUtil.isNotEmpty(isCt)){
			whee.append(" and isCt = ?");
			param.add(isCt);
		}
		if(ChristStringUtil.isNotEmpty(isCu)){
			whee.append(" and isCu = ?");
			param.add(isCu);
		}
		if(ChristStringUtil.isNotEmpty(isCmOrderConfirm)){
			whee.append(" and isCmOrderConfirm = ?");
			param.add(isCmOrderConfirm);
		}
		if(ChristStringUtil.isNotEmpty(isCtOrderConfirm)){
			whee.append(" and isCtOrderConfirm = ?");
			param.add(isCtOrderConfirm);
		}
		if(ChristStringUtil.isNotEmpty(isCuOrderConfirm)){
			whee.append(" and isCuOrderConfirm = ?");
			param.add(isCuOrderConfirm);
		}

		if(ChristStringUtil.isNotEmpty(isSelectConfirm)){
			whee.append(" and isSelectConfirm = ?");
			param.add(isSelectConfirm);
		}
		if(ChristStringUtil.isNotEmpty(cmCreateDate)){
			whee.append(" and TO_DAYS(cmCreateDate) = TO_DAYS(?)");
			param.add( cmCreateDate );
		}
		if(ChristStringUtil.isNotEmpty(ctCreateDate)){
			whee.append(" and TO_DAYS(ctCreateDate) = TO_DAYS(?)");
			param.add( ctCreateDate );
		}
		if(ChristStringUtil.isNotEmpty(cuCreateDate)){
			whee.append(" and TO_DAYS(cuCreateDate) = TO_DAYS(?)");
			param.add( cuCreateDate );
		}

		if(ChristStringUtil.isNotEmpty(beginDate)){
			whee.append(" and TO_DAYS(createDate) >= TO_DAYS(?)");
			param.add( beginDate );
		}
		if(ChristStringUtil.isNotEmpty(endDate)){
			whee.append(" and TO_DAYS(createDate) <= TO_DAYS(?)");
			param.add(endDate);
		}
		Page<Project> pager = Project.dao.paginate(
				getParaToInt("pageNum", 1),
				getParaToInt("numPerPage", 20),
                "select * ",
                " from " + TABLE_Project +" where 1=1 "+ whee.toString()+"order by modifyDate desc",
                param.toArray());

		setAttr("pager",pager);
		setAttr("name",name);
		setAttr("memo",memo);
		setAttr("demandMemo",demandMemo);
		setAttr("code",code);
		setAttr("demandOwner",demandOwner);
		setAttr("isSelect",isSelect);
		setAttr("isSelectConfirm",isSelectConfirm);
		setAttr("isEntrust",isEntrust);
		setAttr("isCm",isCm);
		setAttr("isCt",isCt);
		setAttr("isCu",isCu);

		setAttr("isCmOrderConfirm",isCmOrderConfirm);
		setAttr("isCtOrderConfirm",isCtOrderConfirm);
		setAttr("isCuOrderConfirm",isCuOrderConfirm);

		setAttr("cmCreateDate",cmCreateDate);
		setAttr("ctCreateDate",ctCreateDate);
		setAttr("cuCreateDate",cuCreateDate);

		setAttr("beginDate",beginDate);
		setAttr("endDate",endDate);
		setAttr("homeCounty",homeCounty);
		render("../project_demand_list.html");

	}

	@Before(AuthorizeInterceptor.class)
	public void add() {
		render("../project_demand_input.html");
	}

	public void imp() {
		render("../project_demand_input_imp.html");
	}

	public void impData() {
		String path = new SimpleDateFormat("yyyy/MM/dd").format(new Date());
		UploadFile file = getFile();
		String fileName = null;
		if (file !=null) {
			File source = file.getFile();
			fileName = file.getFileName();
			try {

				FileInputStream fis = new FileInputStream(source);
				File targetDir = new File(PathKit.getWebRootPath()  + "/u/" + path);
				if (!targetDir.exists()) {
					targetDir.mkdirs();
				}
				File target = new File(targetDir, fileName);
				if (!target.exists()) {
					target.createNewFile();
				}
				FileOutputStream fos = new FileOutputStream(target);
				byte[] bts = new byte[300];
				while (fis.read(bts, 0, 300) != -1) {
					fos.write(bts, 0, 300);
				}
				fos.close();
				fis.close();
				source.delete();

				String templateFileName = PathKit.getWebRootPath()  + "/u/" + path +"/" +fileName;
				String sql = "insert into jms_project (homeCounty,name,cmName,ctName,cuName," +
						"isCm,isCt,isCu,cmCreateDate,ctCreateDate,cuCreateDate," +
						"planLong,planLat,code,demandMemo," +
						"cmSource,ctSource,cuSource,cmSubmitDate,ctSubmitDate,cuSubmitDate," +
						"isSelect,isSelectConfirm," +
						"towerType,longitude,latitude,addr," +
						"isCmOrderConfirm,isCtOrderConfirm,isCuOrderConfirm," +
						"share,shareName,projectDate,demandOwner,projectOwner,memo) values(?,?,?,?,?," +
						"?,?,?,?,?,?," +
						"?,?,?,?," +
						"?,?,?,?,?,?," +
						"?,?," +
						"?,?,?,?," +
						"?,?,?," +
						"?,?,?,?,?,?)";
				Map<String, Object> map = PoiExt.ReadExcel(templateFileName, "Sheet1",sql,3,1,36);
				renderDWZSuccessJson("总记录数:"+map.get("count")+"；成功导入:"+map.get("success")+"；失败数"+map.get("lost"));

			} catch (FileNotFoundException e) {
				renderDWZErrorJson("上传出现错误，请稍后再上传");
			} catch (IOException e) {
			}catch (Exception e){
				renderDWZErrorJson("导入失败，请重新上传");
			}
		}


	}

	public void search() {
		render("../project_demand_search.html");
	}

	public void save() {
		Project s = getModel(Project.class, "project");
		ModelUtils.fillForSave(s);
		s.save();
		renderDWZSuccessJson("添加成功！");
	}

	@Before(AuthorizeInterceptor.class)
	public void edit() {
		String id = getPara("id");
		Project project = Project.dao.findById(getPara("id"));
		setAttr("project", project);
		render("../project_demand_input.html");
	}



	public void update() {
		Project s = getModel(Project.class, "project");
		ModelUtils.fillForUpdate(s);
		s.update();
		renderDWZSuccessJson("修改成功！");
	}


	public void export() {

		//导出条件
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		String isCm = getPara("demand.isCm");
		String isCt = getPara("demand.isCt");
		String isCu = getPara("demand.isCu");

		if(ChristStringUtil.isNotEmpty(isCm)){
			whee.append(" and isCm = ?");
			param.add(isCm);
		}
		if(ChristStringUtil.isNotEmpty(isCt)){
			whee.append(" and isCt = ?");
			param.add(isCt);
		}
		if(ChristStringUtil.isNotEmpty(isCu)){
			whee.append(" and isCu = ?");
			param.add(isCu);
		}

		String sql = "select *  from " + TABLE_Project +" where 1=1 "+ whee.toString()+"order by modifyDate desc";
		List<Record> demandDetail = Db.find(sql, param.toArray());
/*
		sql = "select date_format(now(),'%Y%m%d') sumDate,homeCounty,cnt,selectCnt,\n" +
				"projectCnt,notprojectCnt,projectReachCnt,notprojectReachCnt,\n" +
				"cmCnt,cmOrderCnt,\n" +
				"ctCnt,ctOrderCnt,\n" +
				"cuCnt,cuOrderCnt\n" +
				"from (\n" +
				"select a.homeCounty,count(*) cnt,\n" +
				"sum(case when a.isCm='是' then 1 else 0 end) cmCnt,\n" +
				"sum(case when a.isProject='否' and a.isSelectConfirm='是' then 1 else 0 end) selectCnt,\n" +
				"sum(case when a.isCm='是' and a.isCmOrderConfirm='是' then 1 else 0 end) cmOrderCnt,\n" +
				"sum(case when a.isCt='是' then 1 else 0 end) ctCnt,\n" +
				"sum(case when a.isCt='是' and a.isCtOrderConfirm='是' then 1 else 0 end) ctOrderCnt,\n" +
				"sum(case when a.isCu='是' then 1 else 0 end) cuCnt,\n" +
				"sum(case when a.isCu='是' and a.isCuOrderConfirm='是' then 1 else 0 end) cuOrderCnt,\n" +
				"sum(case when a.isProject='是' then 1 else 0 end) projectCnt,\n" +
				"sum(case when a.isProject='否' then 1 else 0 end) notprojectCnt,\n" +
				"sum(case when a.isProject='是' and b.isReach='是' then 1 else 0 end) projectReachCnt,\n" +
				"sum(case when a.isProject='否' and b.isReach='是' then 1 else 0 end) notprojectReachCnt\n" +
				"  from jms_project a left join jms_project_build b\n" +
				"on a.pid=b.pid\n" +
				"group by a.homeCounty\n" +
				")t\n" +
				"\n";

		List<Record> demandSum = Db.find(sql);
		String sumDate = DateUtil.getStringDateShort();
		Db.update("delete a from jms_project_demand_sum a where sumDate =?",sumDate);
		for(Record r:demandSum){

			Db.save("jms_project_demand_sum",r);
		}
*/
		Map<String, Object> beans = new HashMap<String, Object>();
		beans.put("demandDetail", demandDetail);
		//beans.put("demandSum", demandSum);

		String templateFileName = PathKit.getRootClassPath() + "/tpl_project_demand.xls";
		String filename = "project_demand.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));


	}



	@Before(AuthorizeInterceptor.class)
	public void delete() {

		String[] ids = getParaValues("ids");
		for(String id:ids){
			Project.dao.deleteById(id);
		}
		renderDWZSuccessJson("删除成功!");

	}

	public void down(){

		String templateFileName = PathKit.getRootClassPath() + "/imp_demand.xls";

		renderFile(new File(templateFileName));
	}

}
